﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;

namespace com.fly.sms
{
    public class Db
    {
        private static SqlConnection getConnection() {
            //string url = "server=121.11.69.86;database=PlatForm-SMS-Test;uid=gaoxiaofei;pwd=gaoxiaofei";
            //string url = "server=121.11.69.86;database=PlatForm-SMS;uid=gaoxiaofei;pwd=gaoxiaofei";
            string url = ConfigurationManager.ConnectionStrings["sms"].ConnectionString;
            return new SqlConnection(url);
        }


        private static DataSet query(string sql)
        {
            string strError = "";
            SqlConnection conn = getConnection();
            try
            {
                //若数据库连接的当前状态是关闭的，则打开连接
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
                SqlCommand selectCommand = new SqlCommand(sql, conn);
                selectCommand.CommandType = CommandType.Text;
                mySqlDataAdapter.SelectCommand = selectCommand;
                DataSet myDS = new DataSet();
                mySqlDataAdapter.Fill(myDS);
                return myDS;
            }
            catch (Exception e)
            {
                strError = "数据检索失败：" + e.Message;
                //Console.WriteLine(strError);
                throw e;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        public static DataTable find(string sql)
        {
            DataSet ds = query(sql);
            DataTable dt = ds.Tables[0];
            return dt;
        }

        //分页查询
        //startRows开始函数
        //pagePerSize分页大小
        public static DataTable find(string sql, int startRows, int pagePerSize)
        {
            string sqlTep = "";
            string orderby = "";
            int orderbyIndex = -1;
            orderbyIndex = sql.IndexOf("order by");

            if (orderbyIndex != -1)
            {
                sqlTep = sql.Substring(0, orderbyIndex);
                orderby = sql.Substring(orderbyIndex);
            }
            else {
                sqlTep = sql;
                orderby = "order by newID() desc";
            }
            return find(pages(sqlTep, orderby, startRows, pagePerSize));
        }

        public static DataRow findSingle(string sql)
        {
            DataTable dt = find(sql);
            if (dt.Rows.Count < 1)
            {
                return null;
            }
            else
            {
                return dt.Rows[0];
            }
        }


        public static bool update(string sql)
        {
            string strError = "";
            SqlConnection conn = getConnection();
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.Text;
                int intCount = cmd.ExecuteNonQuery();
                return !(intCount < 1);
            }
            catch (Exception e)
            {
                strError = "更新数据库失败：" + e.Message;
                throw e;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
        //分页SQL处理
        private static string pages(string sql,string orderby, int startRows, int pagePerSize)
        {
            string temp = "select t.* from ("
                    + "select row_number() over("+orderby+")"
                    + " as rowNum,l.*  from (" + sql + ") l"
                    + ") as t where rowNum>" + startRows
                    + " and rowNum<=" + (pagePerSize + startRows);
            return temp;
        }

        //test
        public static void Main()
        {

            DataSet ds = Db.query("SELECT * FROM  SMSend where CreateTime>=" + Convert.ToDateTime("2011-11-01") + " and CreateTime<" + Convert.ToDateTime("2011-12-01") + " and Name like '%%' order by CreateTime desc");
            DataTable dt = ds.Tables[0];
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine(row["Name"]+"");
            }
        }
    }
}